package model;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import org.apache.struts.upload.FormFile;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.DegreeForm;
import forms.StudentForm;

public class StudentModel {

	public enum StringToID { COUNTRY, SPECIAL, STUDENT, UNIVERSITY, DISCIPLINE, LOCATION};
	
	// ====== DEBUG flag ===== //
	private static boolean DEBUG = true;
	
	// ====== Primary Student Queries ===== //
	private static String selectStubyStr = "SELECT * " +
						" FROM students WHERE first_name = ? AND middle_name = ? AND last_name = ?";
	private static String selectStubyID = "SELECT * " +
						" FROM students WHERE id = ?";
	private static String insertStu = "INSERT INTO students " +
						"(first_name, middle_name, last_name, citizenship, residence, " +
						"street_addr, city, state, zip_code, country_code, area_code, " +
						"telephone, residency, specialization, admission_status) VALUES " + 
						"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	// ====== Primary Degree Queries ====== //
	private static String insertDeg = "INSERT INTO degrees " +
							"(university, discipline, grad_month, grad_year, gpa, title, " +
							"transcript, student) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
	private static String selectDeg = "SELECT * FROM degrees " +
							"WHERE student = ?";
	
	// ======= Helper Queries ====== //
	private static String selectCountbyStr = "SELECT * FROM countries WHERE country_name = ?";
	private static String selectSpecbyStr = "SELECT * FROM specializations WHERE specialization = ?";
	private static String selectUnivbyStr = "SELECT * FROM universities WHERE university = ?";
	private static String selectDiscipbyStr = "SELECT * FROM disciplines WHERE discipline = ?";
	
	private static String selectCountbyID = "SELECT * FROM countries WHERE id = ?";
	private static String selectSpecbyID = "SELECT * FROM specializations WHERE id = ?";
	private static String selectUnivbyID = "SELECT * FROM universities WHERE id = ?";
	private static String selectDiscipbyID = "SELECT * FROM disciplines WHERE id = ?";
	private static String selectLocbyID = "SELECT * FROM locations WHERE id = ?";
	
	/**
	 * insertStudent
	 * 
	 * @param student
	 * @param degrees
	 * @throws DbException
	 */
	public static void insertStudent(String user, StudentForm student, Vector<DegreeForm> degrees)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			conn.setAutoCommit(false);
			
			int citizen_id = getID(conn, StringToID.COUNTRY, student.getCitizen(), "", "");
			int residence_id = getID(conn, StringToID.COUNTRY, student.getResidence(), "", "");
			int spec_id = getID(conn, StringToID.SPECIAL, student.getSpecialization(), "", "");
			
			if (DEBUG) {
				System.err.println("StudentModel: citizen_id(" + citizen_id + ")");
				System.err.println("StudentModel: residence_id(" + residence_id + ")");
				System.err.println("StudentModel: spec_id(" + spec_id + ")");
			}
			
			// Create the prepared statement
			PreparedStatement pstmt = conn.prepareStatement(insertStu);

			pstmt.setString(1, student.getFname());
			pstmt.setString(2, student.getMname());
			pstmt.setString(3, student.getLname());
			pstmt.setInt(4, citizen_id);
			pstmt.setInt(5, residence_id);
			pstmt.setString(6, student.getAddress());
			pstmt.setString(7, student.getCity());
			pstmt.setString(8, student.getState());
			pstmt.setInt(9,  Integer.parseInt(student.getZip_code()));
			
			if (student.getCountry_code() == null || student.getCountry_code().equals("")) {
				pstmt.setInt(10, 0);
			}
			else {
				pstmt.setInt(10, Integer.parseInt(student.getCountry_code()));
			}
			
			pstmt.setInt(11, Integer.parseInt(student.getArea_code()));
			pstmt.setInt(12, Integer.parseInt(student.getTelephone()));
			pstmt.setBoolean(13, Boolean.parseBoolean(student.getResidency()));
			pstmt.setInt(14, spec_id);
			pstmt.setString(15, "Pending");
			
			// Execute prepared statement
			pstmt.executeUpdate();

			// Look up student id
			int student_id = getID(conn, StringToID.STUDENT, student.getFname(), 
					student.getMname(), student.getLname());
			
			if (DEBUG) {
				System.err.println("StudentModel: student_id(" + student_id + ")");
			}
			
			// Insert degrees for this student
			for (int count = 0; count < degrees.size(); count++) {
			
				if (DEBUG) {
					System.err.println("StudentModel: inserting degree" + count);
				}
				
				try {
					insertDegree(conn, student_id, degrees.get(count));
				} catch (FileNotFoundException ex) {
					throw new DbException(ex);
				} catch (IOException ex) {
					throw new DbException(ex);
				}
			}
			
			// Update user information
			UserModel.updateUserStatus(conn, user, student_id);
			
			// Commit transaction
			conn.commit();
			conn.setAutoCommit(true);
			
			pstmt.close();
			conn.close();
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	/**
	 * insertDegree
	 * 
	 * @param conn
	 * @param student_id
	 * @param dform
	 * @throws DbException
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 */
	private static void insertDegree(Connection conn, int student_id, DegreeForm dform) 
		throws DbException, FileNotFoundException, IOException {
		
		try {
			int univ_id = getID(conn, StringToID.UNIVERSITY, dform.getUniversity(), "", "");
			int disc_id = getID(conn, StringToID.DISCIPLINE, dform.getMajor(), "", "");
			
			PreparedStatement pstmt = conn.prepareStatement(insertDeg);
			
			//university, discipline, grad_month, grad_year, gpa, title, student
			pstmt.setInt(1, univ_id);
			pstmt.setInt(2, disc_id);
			pstmt.setInt(3, Integer.parseInt(dform.getMonth()));
			pstmt.setInt(4, Integer.parseInt(dform.getYear()));
			pstmt.setFloat(5, Float.parseFloat(dform.getGpa()));
			pstmt.setString(6, dform.getTitle());
			FormFile transcript  = dform.getTranscript();
			pstmt.setBinaryStream(7, transcript.getInputStream(), transcript.getFileSize());
			pstmt.setInt(8, student_id);
			
			pstmt.executeUpdate();
			pstmt.close();
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}
	
	/**
	 * getID
	 * 
	 * @param conn
	 * @param sid
	 * @param val1
	 * @param val2
	 * @param val3
	 * @return
	 * @throws DbException
	 */
	private static int getID(Connection conn, StringToID sid, String val1, String val2, String val3) 
		throws DbException{
		
		int ret_id = 0;
		
		try {
			PreparedStatement pstmt;

			//enum StringToID { COUNTRY,  SPECIAL, STUDENT, UNIVERSITY, DISCIPLINE};
			switch (sid) {
				case COUNTRY:
					pstmt = conn.prepareStatement(selectCountbyStr);
					break;
				case SPECIAL:
					pstmt = conn.prepareStatement(selectSpecbyStr);
					break;
				case STUDENT:
					pstmt = conn.prepareStatement(selectStubyStr);
					pstmt.setString(2, val2);
					pstmt.setString(3, val3);
					break;
				case UNIVERSITY:
					pstmt = conn.prepareStatement(selectUnivbyStr);
					break;
				case DISCIPLINE:
					pstmt = conn.prepareStatement(selectDiscipbyStr);
					break;
				default:
					return 0;
			}
			
			pstmt.setString(1, val1);
			
			ResultSet rset = pstmt.executeQuery();
			
			while (	rset.next() ) {
				ret_id = rset.getInt("id");
			}
			
			pstmt.close();
			rset.close();
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
		
		return ret_id;
	}
	
	public static int getUnivLocID(StringToID sid, String val1) 
		throws DbException {
		
		int ret_id = 0;
		
		
		try {
			Connection conn = DbConnectionPool.getConnection();
			
			PreparedStatement pstmt  = conn.prepareStatement(selectUnivbyStr);
			pstmt.setString(1, val1);
			
			ResultSet rset = pstmt.executeQuery();
			
			while (	rset.next() ) {
				ret_id = rset.getInt("location");
			}
			
			pstmt.close();
			rset.close();
			conn.close();
			
		} catch (NamingException ex) {
			throw new DbException(ex);
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
		
		
		return ret_id;
	}
	
	public static String getString(StringToID sid, int val) 
		throws DbException {

		String ret_string = "";
		
		try {
			Connection conn = DbConnectionPool.getConnection();
			
			PreparedStatement pstmt;
			
			//enum StringToID { COUNTRY,  SPECIAL, STUDENT, UNIVERSITY, DISCIPLINE};
			switch (sid) {
				case COUNTRY:
					pstmt = conn.prepareStatement(selectCountbyID);
					break;
				case SPECIAL:
					pstmt = conn.prepareStatement(selectSpecbyID);
					break;
				case UNIVERSITY:
					pstmt = conn.prepareStatement(selectUnivbyID);
					break;
				case DISCIPLINE:
					pstmt = conn.prepareStatement(selectDiscipbyID);
					break;
				case LOCATION:
					pstmt = conn.prepareStatement(selectLocbyID);
					break;
				default:
					return ret_string;
			}
			
			pstmt.setInt(1, val);
			
			ResultSet rset = pstmt.executeQuery();
			
			while (	rset.next() ) {
				switch (sid) {
					case COUNTRY:
						ret_string = rset.getString("country_name");
						break;
					case SPECIAL:
						ret_string = rset.getString("specialization");
						break;
					case UNIVERSITY:
						ret_string = rset.getString("university");
						break;
					case DISCIPLINE:
						ret_string = rset.getString("discipline");
						break;
					case LOCATION:
						ret_string = rset.getString("location");
						break;
					default:
						continue;
				}
			}
			
			if (DEBUG) {
				System.err.println("StudentModel: string(" + ret_string + ")");
			}
			
			rset.close();
			pstmt.close();
			conn.close();
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
		
		return ret_string;
	}
	
	/**
	 * getStudent
	 * 
	 * @param student_id
	 * @return
	 * @throws DbException
	 */
	public static CachedRowSet getStudent(int student_id) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pstmt = conn.prepareStatement(selectStubyID);
			pstmt.setInt(1, student_id);
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allStudents = pstmt.executeQuery();

			CachedRowSet student = new CachedRowSetImpl();
			student.populate(allStudents);

			allStudents.close();
			pstmt.close();
			conn.close();

			return student;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static CachedRowSet getDegrees(int student_id) throws DbException {
		
		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pstmt = conn.prepareStatement(selectDeg);
			pstmt.setInt(1, student_id);
			
			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allDegrees = pstmt.executeQuery();
			
			CachedRowSet crsStudents = new CachedRowSetImpl();
			crsStudents.populate(allDegrees);

			allDegrees.close();
			pstmt.close();
			conn.close();

			return crsStudents;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
}
